package cn.mgm.base;

import org.junit.Test;

import java.sql.*;

public class JDBCOperationTest {

    private final String url="jdbc:mysql://139.224.197.217:3336/guigu-auth?useSSL=false";

    private final String username="root";

    private final String password="kaa3akv8";
    /**
     * 单行单列查询
     */
    @Test
    public void testQuerySingleRowAndCol() throws SQLException {

        // 1. 注册驱动 (可以省略)

        // 2. 创建连接
        Connection connection = DriverManager.getConnection(url,username,password);

        // 3. 预编译 SQL 语句并得到 preparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT count(id) as count from sys_user");

        // 4执行 SQL 语句,获取结果
        ResultSet resultSet = preparedStatement.executeQuery();

        // 5. 处理结果
        while (resultSet.next()){
            int count = resultSet.getInt("count");
            System.out.println(count);
        }

        // 释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }


    @Test
    public void testQuerySingleRow() throws SQLException {
        // 1.注册驱动
        // 2.获取连接
        Connection connection = DriverManager.getConnection(url, username, password);

        // 3. 预编译 sql
        PreparedStatement preparedStatement = connection.prepareStatement("select * from  sys_user where  id =?;");

        preparedStatement.setInt(1,1);
        //4. 执行 SQL

        ResultSet resultSet = preparedStatement.executeQuery();

        // 5.处理结果
        while (resultSet.next()){
            System.out.println(resultSet.getString("name")+resultSet.getString("username"));
        }
        // 6释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();

    }


    @Test
    public void testQueryMoreRow() throws SQLException {
        // 1.注册驱动
        // 2. 获取连接
        Connection connection = DriverManager.getConnection(url, username, password);

        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM sys_user where status= ?");

        preparedStatement.setInt(1,1);

        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()){
            System.out.println(resultSet.getString("name"));
        }

        resultSet.close();
        preparedStatement.close();
        connection.close();

    }


    @Test
    public void testInsert() throws SQLException {
        Connection connection = DriverManager.getConnection(url, username, password);

        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO sys_user(name, phone, head_url, dept_id, post_id, description, status) VALUES (?,?,?,?,?,?,?)");

        preparedStatement.setString(1,"yushi");
        preparedStatement.setString(2,"17092559941");
        preparedStatement.setString(3,"111");
        preparedStatement.setInt(4,5);
        preparedStatement.setInt(5,0);
        preparedStatement.setString(6,"描述");
        preparedStatement.setInt(7,1);

        int i = preparedStatement.executeUpdate();

        if (i>0){
            System.out.println("成功");
        }else {
            System.out.println("失败");
        }

        preparedStatement.close();
        connection.close();

    }


    @Test
    public void testUpdate() throws SQLException {
        Connection connection = DriverManager.getConnection(url, username, password);
        PreparedStatement preparedStatement = connection.prepareStatement("UPDATE sys_user SET username=? where id=?");

        preparedStatement.setString(1,"mgm");
        preparedStatement.setInt(2,1);
        int i = preparedStatement.executeUpdate();
        if (i>0){
            System.out.println("成功");
        }else {
            System.out.println("失败");
        }

        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testDelete() throws SQLException {
        Connection connection = DriverManager.getConnection(url, username, password);
        PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM sys_user where id=?");

        preparedStatement.setInt(1,1);
        int i = preparedStatement.executeUpdate();
        if (i>0){
            System.out.println("成功");
        }else {
            System.out.println("失败");
        }

        preparedStatement.close();
        connection.close();
    }
}
